package com.me.db;

import com.me.db.MeDB.EventTable;
import com.me.db.MeDB.LocationTable;
import com.me.db.MeDB.TitleTable;

public class MeQuery {

	// create event table
	public static final String CREATE_EVENT_TABLE = "create table "
			+ EventTable.EVENT_TABLE_NAME + " (" + EventTable.EVENT_COLUMN_ID
			+ " integer primary key, " + EventTable.EVENT_TITLE + " text, "
			+ EventTable.EVENT_VENUE + " text, " + EventTable.EVENT_ALL_DAY
			+ " integer, " + EventTable.EVENT_START + " integer, "
			+ EventTable.EVENT_END + " integer, " + EventTable.EVENT_DURATION
			+ " text, " + EventTable.EVENT_RULE + " text, "
			+ EventTable.EVENT_TIME_ZONE + " text, "
			+ EventTable.EVENT_CALENDAR_ID + " long)";
	
	// create title table
	public static final String CREATE_TITLE_TABLE = "create table "
			+ TitleTable.TITLE_TABLE_NAME + " (" + TitleTable.TITLE_ID
			+ " integer primary key, " + TitleTable.TITLE_PREFIX + " text not null, "
			+ TitleTable.TITLE_NAME + " text unique,"
			+ TitleTable.TITLE_COUNT + " integer default 1)";

	// create location table
	public static final String CREATE_LOCATION_TABLE = "create table "
			+ LocationTable.LOCATION_TABLE_NAME + " (" + LocationTable.LOCATION_ID
			+ " integer primary key, " + LocationTable.LOCATION_PREFIX + " text not null, "
			+ LocationTable.LOCATION_NAME + " text unique,"
			+ LocationTable.LOCATION_COUNT+ " integer default 1)";

	
	// create title table index (not unique)
	public static final String CREATE_TITLE_PREFIX_INDEX = "create index "
			+ TitleTable.TITLE_TABLE_NAME + "_" + TitleTable.TITLE_PREFIX + " ON " + TitleTable.TITLE_TABLE_NAME
			+ "(" + TitleTable.TITLE_PREFIX + ")";
	
	// create location table index (not unique)
	public static final String CREATE_LOCATION_PREFIX_INDEX = "create index "
			+ LocationTable.LOCATION_TABLE_NAME + "_" + LocationTable.LOCATION_PREFIX + " ON " + LocationTable.LOCATION_TABLE_NAME
			+ "(" + LocationTable.LOCATION_PREFIX + ")";	
		
	// ***************************************************************************************************************

	// query
	public static final String ORDER_ASCENDING = " asc";
	public static final String ORDER_DESCENDING = " desc";

	public static final String COLUMN_ID = "EventTable.EVENT_COLUMN_ID";
	public static final String COLUMN_START = EventTable.EVENT_START;
	
	// select clause	
	public static final String[] PROJECTION = new String[] {
			EventTable.EVENT_COLUMN_ID,
			EventTable.EVENT_TITLE, EventTable.EVENT_VENUE,
			EventTable.EVENT_ALL_DAY, EventTable.EVENT_START,
			EventTable.EVENT_END };

	// where clause
	public static final String SELECTION = EventTable.EVENT_COLUMN_ID + " = ?";
	
	public static final int INDEX_EVENT_ID = 0;
	public static final int INDEX_EVENT_TITLE = 1;
	public static final int INDEX_EVENT_VENUE = 2;
	public static final int INDEX_EVENT_ALL_DAY = 3;
	public static final int INDEX_EVENT_START = 4;
	public static final int INDEX_EVENT_END = 5;
	

	// for title
	public static final String[] T_PROJECTION = new String[] {
		TitleTable.TITLE_ID,
		TitleTable.TITLE_PREFIX,
		TitleTable.TITLE_NAME, 
		TitleTable.TITLE_COUNT 
	};

	public static final int INDEX_TITLE_ID = 0;
	public static final int INDEX_TITLE_PREFIX = 1;
	public static final int INDEX_TITLE_NAME = 2;
	public static final int INDEX_TITLE_COUNT = 3;
	
	// for location
	public static final String[] LO_PROJECTION = new String[] {
		LocationTable.LOCATION_ID,
		LocationTable.LOCATION_PREFIX,
		LocationTable.LOCATION_NAME,
		LocationTable.LOCATION_COUNT
	};

	public static final int INDEX_LOCATION_ID = 0;
	public static final int INDEX_LOCATION_PREFIX = 1;
	public static final int INDEX_LOCATION_NAME = 2;
	public static final int INDEX_LOCATION_COUNT = 3;
	


	// ***************************************************************************************************************

	// update

	// ***************************************************************************************************************

	// delete
	public static final String DELETE_EVENT_TABLE = "drop table if exists "
			+ EventTable.EVENT_TABLE_NAME;
	public static final String DELETE_TITLE_TABLE = "drop table if exists "
			+ TitleTable.TITLE_TABLE_NAME;
	public static final String DELETE_LOCATION_TABLE = "drop table if exists "
			+ LocationTable.LOCATION_TABLE_NAME;
	
	// ***************************************************************************************************************

}
